package common;

//import SensorData;
//import Recommendation;
import java.util.ArrayList;
import java.lang.Math;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DataManager {
/**
 * This acts as the buffer between the main program and the database: it makes all sql calls
 * beyond initializing the database, to keep data flow consistent. It uses the SensorData
 * class as a dataholder for SensorDatas, and ints for consumption data.
 * 
 */

	private String url;
	private String user;
	private String password;
	private Connection con;
	
	public DataManager() {
//--TODO Set these values when the db is fully set up.
		url = null;
		user = null;
		password = null;
		try {
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void makeRecommendation(Recommendation rec) throws SQLException{
		PreparedStatement pst = null;
		ResultSet rs = null;
		
        pst = con.prepareStatement("INSERT INTO Recommendations(HouseId, RecString)"
        		+ " VALUES (?, ?)");
        pst.setInt(1, rec.getHouseId());
        pst.setString(2, rec.getMessage());
        
        
        rs = pst.executeQuery();
        
        return;
	}
	
	public ArrayList<String> getRecomendations(int houseId) throws SQLException{
		PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<String> recs = new ArrayList<String>();
        int index = 0;
        
        pst = con.prepareStatement("SELECT * FROM Recommendations WHERE HouseId = ?");
        pst.setInt(1, houseId);
        rs = pst.executeQuery();
        
        while(rs.next()) {
        	recs.add(rs.getString(1));
        }
        
        return recs;
	}
	
	public ArrayList<Integer> getHourlyAverage(boolean isWeekday) throws SQLException{
		PreparedStatement pst = null;
        ResultSet rs = null;
       	ArrayList<Integer> averages = new ArrayList<Integer>();
        int sum = 0;
        int count = 0;
        
        pst = con.prepareStatement("SELECT COUNT(HouseId) FROM Houses");
        rs = pst.executeQuery();
        
        if(rs.next()) {	
        	count = rs.getInt(1);
        }
        	
        for(int i = 0; i < 23; i++) {
        	pst = con.prepareStatement("SELECT Wattage FROM Sensor_Data WHERE TIME = ? AND "
        			+ "Weekday = ?");
        	pst.setInt(1, i);
        	pst.setBoolean(2, isWeekday);
        	rs = pst.executeQuery();
        	
        	sum = 0;
        	while (rs.next()) {
        		sum += rs.getInt(1);
        	}
        	averages.add(sum/count);
        }
            return(averages);
	}
	
	public int getOverallAverage() throws SQLException{
		PreparedStatement pst = null;
        ResultSet rs = null;
        int sum = 0;
        int count = 0;
        
        pst = con.prepareStatement("SELECT COUNT(HouseId) FROM Houses");
        rs = pst.executeQuery();

        if(rs.next()) {
        	count = rs.getInt(1);
        }
        
        pst = con.prepareStatement("SELECT SUM(Wattage) FROM Sensor_Data");
        rs = pst.executeQuery();
        
        if(rs.next()) {
        	sum = rs.getInt(1);
        }
        
        return (int)Math.floor((sum/count));
	}
	
	public ArrayList<SensorData> getDayData(boolean dayCheck, int houseId) throws SQLException{
		
		PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<SensorData> houseData = new ArrayList<SensorData>();
        int index = 0;
        
        pst = con.prepareStatement("SELECT * FROM Sensor_Data WHERE Weekday = ? AND "
        		+ "SensorId IN (SELECT SensorId FROM Sensors WHERE RoomId IN ("
        		+ "SELECT RoomId FROM Rooms WHERE HouseId = ?))");
        pst.setBoolean(1, dayCheck);
        pst.setInt(2, houseId);
        rs = pst.executeQuery();
     
        while (rs.next()) {
        	houseData.add(new SensorData());
        	houseData.get(index).setId(rs.getInt(2));
        	houseData.get(index).setMetaData(rs.getInt(3), rs.getInt(4), rs.getBoolean(6), 
        			rs.getInt(5));
        	index++;
        }
        
        for(int i = 0; i < houseData.size(); i++) {
        	 pst = con.prepareStatement("SELECT TagName FROM Tags WHERE TagId IN ("
        	 		+ "SELECT TagId FROM Sensor_Tag WHERE SensorId = ?)");
        	 pst.setInt(1, houseData.get(i).getID());
        	 rs = pst.executeQuery();
        	 
        	 while(rs.next()) {
        		 if(rs.getString(1)!= null) {
        			 houseData.get(i).addTag(rs.getString(1));
        		 }
        		 else {
        			 houseData.get(i).addTag("Default Tag");
        		 }
        		// houseData.get(i).addTag(rs.getInt(0));
        	 }
        }	
        return houseData;
	}
	
	public ArrayList<SensorData> getWeekdayData(int houseId) {
            try{
		return getDayData(true, houseId);
            }catch(SQLException e){
                System.out.println("SQL Error " + e);
                e.printStackTrace();
                return null;
            }
	}
	
	public ArrayList<SensorData> getWeekendData(int houseId) {
	    try{
                return getDayData(false, houseId);
	    }catch(SQLException e){
	        System.out.println("SQL Error " + e);
	        return null;
	    }
	}

	
	public ArrayList<Integer> getUsers() throws SQLException{
		PreparedStatement pst = null;
        ResultSet rs = null;
        ArrayList<Integer> userList;
        userList = new ArrayList<Integer>();
        
        pst = con.prepareStatement("SELECT HouseId FROM Houses");
        rs = pst.executeQuery();
        
        while (rs.next()) {
        	userList.add(rs.getInt(1));
        }
        
        return userList;
	}
	
	public void clearRecs() throws SQLException{
		PreparedStatement pst = null;
        ResultSet rs = null;
        
        pst = con.prepareStatement("DELETE * FROM Recommendations");
        rs = pst.executeQuery();
        
        return;
	}
}
